package com.scuthnweb.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.FlushMode;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.scuthnweb.dao.Sy_userDao;
import com.scuthnweb.domain.Sy_user;
import com.scuthnweb.domain.transfer.Sy_userList;
/**
 * 
 * @author YJ.Huang
 *
 */
public class Sy_userDaoImpl extends HibernateDaoSupport implements Sy_userDao{

	@Override
	public void create(Sy_user sy_user) {
		this.getHibernateTemplate().save(sy_user); 
	}

	@Override
	public List findByNameAndGradeAndMajor(String name, String grade, String major) {
		String hql = "FROM Sy_user s WHERE s.user_name=? AND s.grade=? AND s.major=?";
		List ls = this.getHibernateTemplate().find(hql,new Object[]{name,grade,major});
		return ls;
	}

	@Override
	public List findByUid(Integer uid) {
		String hql = "FROM Sy_user s WHERE s.account.id=?";
		return this.getHibernateTemplate().find(hql, new Object[]{uid});
	}

	@Override
	public void update(final Sy_user sy_user) {
		this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
						session.setFlushMode(FlushMode.AUTO); 
						session.update(sy_user); 
						session.flush(); 
						return null; 
					}
				});
	}

	@Override
	public Sy_userList find(String user_account, String name, Integer authority, String gender, String grade, String major, Integer page,Integer pageSize) {
		Integer start = page*pageSize;
		Integer end   = start+pageSize;
		String hql = "FROM Sy_user s ",
			   hql_count = "SELECT COUNT(s.id) FROM Sy_user s ";
		String condition = "",
			   condition1=null,
			   condition2=null,
			   condition3=null,
			   condition4=null,
			   condition5=null;

		boolean pre = false;
		if(user_account!=null&&(!user_account.equals(""))){
			condition += " s.account.account LIKE ? ";
			pre = true;
		}
		if(name!=null&&(!name.equals(""))){
			if(pre) 
				condition += " AND ";
			condition += " s.user_name LIKE ? ";
			pre = true;
		}
		if(gender!=null&&(!gender.equals(""))){
			if(pre) 
				condition += " AND ";
			condition += " s.gender = ? ";
			pre = true;
		}
		if(grade!=null&&(!grade.equals(""))){
			if(pre) 
				condition += " AND ";
			condition += " s.grade = ? ";
			pre = true;		
		}
		if(major!=null&&(!major.equals(""))){
			if(pre) 
				condition += " AND ";			
			condition += " s.major = ? ";
			pre = true;		
		}
		
		hql += pre?(" WHERE "+condition):"";
		hql_count += pre?(" WHERE "+condition):"";
		Query query = this.getSession().createQuery(hql);
		Query query_count =  this.getSession().createQuery(hql_count);
		
		int count = 0;
		if(user_account!=null&&(!user_account.equals(""))){
			query.setString(count, "%"+user_account+"%");
			count++;
		}
		if(name!=null&&(!name.equals(""))){
			query.setString(count, "%"+name+"%");
			count++;
		}
		if(gender!=null&&(!gender.equals(""))){
			query.setString(count, gender);
			count++;
		}
		if(grade!=null&&(!grade.equals(""))){
			query.setString(count, grade);
		}
		if(major!=null&&(!major.equals(""))){
			query.setString(count, major);
		}
		query.setFirstResult(page*pageSize);
		query.setMaxResults(pageSize);
		
		count = 0;
		if(user_account!=null&&(!user_account.equals(""))){
			query_count.setString(count, "%"+user_account+"%");
			count++;
		}
		if(name!=null&&(!name.equals(""))){
			query_count.setString(count, "%"+name+"%");
			count++;
		}
		if(gender!=null&&(!gender.equals(""))){
			query_count.setString(count, gender);
			count++;
		}
		if(grade!=null&&(!grade.equals(""))){
			query_count.setString(count, grade);
		}
		if(major!=null&&(!major.equals(""))){
			query_count.setString(count, major);
		}
		Sy_userList sl = new Sy_userList();
		sl.setCount((Long)query_count.list().get(0));
		sl.setSy_user(query.list());
		return sl;
	}

	@Override
	public List findByEmail(String email) {
		String hql = "FROM Sy_user s WHERE s.mail LIKE ?";
		List ls = this.getHibernateTemplate().find(hql,new Object[]{"%"+email+"%"});
		return ls;
	}	
}
